﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Data.OleDb;

namespace WebToolsClslib
{
    class ExportCSV
    {
        //就是將DataSet數據集進行拆分添加“；”或者“，”之類的！再將文件保存到格式為CSV文檔中，其代碼為：
        private string Save_CSV(DataSet ds)
        {
            //準備一個變量
            string str_Data = "";
            foreach (DataTable dt in ds.Tables)
            {
                //列出表中列名用特殊符號分割
                foreach (DataColumn column in dt.Columns)
                {
                    str_Data += column.ColumnName + " ; ";
                }

                //列完換行
                str_Data += "/n";
                //循環列出行內容
                foreach (DataRow row in dt.Rows)
                {
                    //循環列出行於列焦點內容
                    foreach (DataColumn column in dt.Columns)
                    {
                        str_Data += row[column].ToString() + " ; ";
                    }
                    //當一行列完換行
                    str_Data += "/r";
                }
                //換行
                str_Data += "/n";
            }
            return str_Data;
        }

        //調用SAVE_CSV方法
        public void SaveToCSV(DataSet ds, HttpResponse response)
        {
            string data = this.Save_CSV(ds);
            string temp = string.Format("attachment;filename={0}", "ExportData.csv");
            response.ClearHeaders();
            response.AppendHeader("Content-disposition", temp);
            response.Write(data);
            response.End();
        }
        //注意事項：

        //如果你的GridView控件的Allowpaging=true ，則將其屬性設置為False！

        //當上面的代碼運行結束后，再將其分頁的屬性：Allowpaging=true；

        //則可以達到显示分頁的效果，而且还會將所有DataSet導入文檔


        //由dataset导出Excel 或Xml        

        public void ExportExcelOrXml(DataSet ds, string typeid, string FileName,HttpResponse response)
        {
            HttpResponse resp;
            resp = response;
            resp.Clear();
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.Charset = "GB2312";
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;

            //定义表对象和行对像，同时用DataSet对其值进行初始化 
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select("");
            // typeid=="1"时导出为EXCEL格式文档；typeid=="2"时导出为XML格式文档 
            if (typeid == "1")
            {
                resp.ContentType = "application/vnd.ms-excel";
                //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        colHeaders += dt.Columns[i].Caption.ToString().ToLower() + "\n";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString().ToLower() + "\t";
                    }
                }
                //向HTTP输出流中写入取得的数据信息 
                resp.Write(colHeaders);
                //逐行处理数据 
                foreach (DataRow row in myRow)
                {
                    //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                    for (i = 0; i < dt.Columns.Count; i++)
                    {

                        if (i == dt.Columns.Count - 1)
                        {
                            ls_item += row[i].ToString() + "\n";
                        }
                        else
                        {
                            ls_item += row[i].ToString() + "\t";
                        }
                    }
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                    resp.Write(ls_item);
                    ls_item = "";
                }
            }
            else if (typeid == "2")
            {
                //从DataSet中直接导出XML数据并且写到HTTP输出流中 
                resp.Write("<?xml version=\"1.0\" encoding=\"gb2312\"?>" + ds.GetXml());
            }
            //写缓冲区中的数据到HTTP头文档中 
            resp.End();
        }

        //excel导入到dataset　　　
        public static DataSet GetWorksheet(string fileName, string sheetName)
        {
            DataSet dataSet = new DataSet();
            OleDbConnection oleDbConnection = null;
            try
            {
                oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
                OleDbCommand selectCommand = new OleDbCommand();
                selectCommand.Connection = oleDbConnection;
                selectCommand.CommandText = "select * from [" + sheetName + "]";
                selectCommand.CommandType = CommandType.Text;
                OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand);
                dataSet = new DataSet();
                adapter.Fill(dataSet);
            }
            catch (Exception exception)
            {
                //oleDbConnection.Close();
                throw exception;
            }
            finally
            {
                oleDbConnection.Close();
            }
            return dataSet;
        }
    }
}
